clear all
cap close
set more off
set mem 800m
set matsize 800
set excelxlsxlargefile on
set linesize 200

cd ".\Data_Program"

* Table 3: 
* Col 1: change line 55 to: by county1: keep if _n<=50
* Col 2: change line 55 to: by county1: keep if _n>=51 & _n<=100
* Col 3: change line 55 to: by county1: keep if _n>=101 & _n<=150
* Col 4: change line 55 to: by county1: keep if _n>=151 & _n<=200
* Col 5: change line 55 to: by county1: keep if _n>=201 & _n<=500

use Econdata\sci,clear
drop if county1==county2

* (I) identify state
gen st1=floor(county1/1000)
gen st2=floor(county2/1000)
drop if inlist(st1,2,15)|st1>56
drop if inlist(st2,2,15)|st2>56


* (II) identify CZ
rename county1 county
merge m:1 county using Econdata\cz_county_crosswalk
drop if _merge==2
drop _merge
rename county county1
rename cz cz1

rename county2 county
merge m:1 county using Econdata\cz_county_crosswalk
drop if _merge==2
drop _merge
rename county county2
rename cz cz2

* (III) merge with distance
merge 1:1 county1 county2 using Econdata\sf12010countydistancemiles
drop if _merge==2
drop _merge


* indicating distant friends
gen outcz=1-(cz1==cz2 & cz1!=. & cz2!=.)     
drop cz1 cz2

* CHANGE THRESHOLD (50, 51-100, 101-500, 201-500)
gsort county1 -sci
by county1: keep if _n<=50
drop mi

expand 44   //2007-2017

sort county1 county2
by county1 county2: gen quarter=q(2007q1)+_n-1
format quarter %tq


* II. Merge with county-level finshare and other data
rename county2 county
merge m:1 county quarter using county
keep if _merge==3
keep quarter county1 county finshare sci out*


* county finshare_ad
merge m:1 county quarter using Proprietary\mintel
drop if _merge==2 
drop _merge
replace finad=0 if finad==.


    * CoreLogic house price growth
merge m:1 county quarter using Proprietary\hpi_county_quarter_exp
drop if _merge==2 
drop _merge qhpi
replace hpgr=hpgr4

    * Census population growth
gen year=yofd(dofq(quarter))
merge m:1 year county using Econdata\pop_county
drop if _merge==2  
drop _merge fracm frach pop


    * QCEW employment growth
merge m:1 year county using Econdata\emp_county_year
drop if _merge==2  
drop _merge year


    * Eqiufax CCP subprime share
merge m:1 quarter county using Proprietary\subprime_county_quarter
drop if _merge==2  
drop _merge 

     * Census education level in 2010
merge m:1 county using Econdata\edu
drop if _merge==2  
drop _merge hsabove


* III weighted average
sort quarter county1
by quarter county1: egen sumsci=sum(sci)
gen weight = sci/sumsci

foreach x of varlist finshare hpgr popgr fracy fracb empgr subprime finad babove{
by quarter county1: egen `x'2=sum(weight*`x')
}
drop sumsci weight
 
* outcz 
by quarter county1: egen sumsci=sum(sci) if outcz==1
gen weight = sci/sumsci
by quarter county1: egen finshare2_outcz=sum(weight*finshare)

by quarter county1: keep if _n==1

keep county1 quarter *2 *2_out* 

rename county1 county

xtset county quarter

save changeN,replace


use county,clear

* merge with division data
merge m:1 county using Econdata\countytodivision.dta
keep if _merge==3   
drop _merge

* merge with neighbor data
merge 1:1 quarter county using changeN.dta
keep if _merge==3
drop _merge


* merge with mintel
merge 1:1 county quarter using Proprietary\mintel
drop if _merge==2 
drop _merge 
replace finad=0 if finad==.
replace nonfinad=0 if nonfinad==.
replace ad=0 if ad==.


* merge with own economic data

    * CoreLogic house price growth
merge 1:1 quarter county using Proprietary\hpi_county_quarter_exp
drop if _merge==2
drop _merge
drop qhpi
replace hpgr=hpgr4

    * Census population growth
gen year=yofd(dofq(quarter))	
merge m:1 year county using Econdata\pop_county
drop if _merge==2
drop _merge
drop fracm frach

    * Census employment growth
merge m:1 year county using Econdata\emp_county_year
drop if _merge==2
drop _merge

    * Equifax CCP subprime share
merge 1:1 quarter county using Proprietary\subprime_county_quarter
drop if _merge==2  
drop _merge 

     * Census education level in 2010
merge m:1 county using Econdata\edu
drop if _merge==2  
drop _merge 

* IRS migration
merge m:1 year county using Econdata\migration
drop if _merge==2  
drop _merge


* addional controls
tab year, gen(yy)
foreach i of numlist 1/10{
    gen baboveyy`i' = babove*yy`i'
	gen babove2yy`i' = babove2*yy`i'
	gen fracbyy`i' = fracb*yy`i'
	gen fracyyy`i' = fracy*yy`i'
}

* Regressions
xtset county quarter

* weights
sort county quarter
by  county: egen w1 = mean(tot)
by  county: egen w2 = mean(pop)
gen w=w1


global control2 hpgr popgr empgr fracyyy* fracbyy* subprime baboveyy* dfinad hpgr2 popgr2 empgr2 fracy2 fracb2 subprime2 babove2yy* dfinad2 linflow loutflow

  * outcome var
foreach x of varlist finshare{
gen	d`x'=`x'-l4.`x'
gen	d`x'2=`x'2-l4.`x'2
gen	d`x'2_outcz=`x'2_outcz-l4.`x'2_outcz
}
gen dfinad = (log(finad+1)-log(l4.finad+1))/100
gen dfinad2 = (log(finad2+1)-log(l4.finad2+1))/100

ivreghdfe dfinshare (dfinshare2=dfinshare2_outcz) $control2 [aw=w], a(division#quarter county) cluster(county) 